import sys 
from PyQt5.QtWidgets import QMainWindow, QApplication, QPushButton, QWidget, QAction, QTabWidget, QVBoxLayout, QLabel 
import getpath
import os
from PyQt5.QtWidgets import QTableWidget, QTableWidgetItem
from PyQt5 import QtWidgets,QtCore,QtGui
import traceback
# Creating the main window 
class App(QMainWindow): 
    def __init__(self): 
        super().__init__() 
        self.title = 'PyQt5 - QTabWidget'
        self.left = 0
        self.top = 0
        self.width = 300
        self.height = 200
        self.setWindowTitle(self.title) 
        self.setGeometry(self.left, self.top, self.width, self.height) 

        self.tab_widget = MyTabWidget(self) 
        self.setCentralWidget(self.tab_widget) 
        bar=self.menuBar()
        #向菜单栏中添加新的QMenu对象，父菜单
        file=bar.addMenu('Action')
        oneAction = QtWidgets.QAction(self)
        oneAction.setText("open")
        oneAction.triggered.connect(self.open)
        file.addAction(oneAction)
        # if sys.platform == "linux":
        # self.menubar = QtWidgets.QMenuBar(self)
        # self.menubar.setGeometry(QtCore.QRect(0, 0, 577, 23))
        # self.menubar.setObjectName("menubar")
        # self.menuedit = QtWidgets.QMenu(self.menubar)
        # self.menuedit.setObjectName("menuedit")
        # self.menu = QtWidgets.QMenu(self.menubar)
        # self.menu.setObjectName("menu")
        # self.setMenuBar(self.menubar)
        self.show() 
    def open_file(self,fname):
        self.tab_widget.open_file(fname)
    def open(self):
        print("open")
        FileDialog =QtWidgets.QFileDialog(self)
        folder=os.path.join(os.curdir)#os.path.expanduser('~'), "Desktop")
        fil = "xlsx Files (*.xlsx);;All Files (*)"
        FileDirectory = FileDialog.getOpenFileName(
            self, 'select import file', folder, fil)  # 选择目录，返回选中的路径
        fname = FileDirectory[0]
        if fname == "":
            pass
        else:
            try:
                self.open_file(fname)
            except:
                traceback.print_exc() 
# Creating tab widgets 

class MyTabWidget(QWidget):
    def open_file(self,fname):
        self.tab_widget.clear()
        self.datas=self.getBook(os.path.join(getpath.getpath(),fname))
        for i in range(len(self.datas)):
            one=self.Table(self.datas[i])
            self.tab_widget.addTab(one,"sheet"+str(i+1))
    def getBook(self,fname):
        from openpyxl import load_workbook
        xl_book = load_workbook(filename=fname)
        datas=[]
        for i in range(len(xl_book.worksheets)):
            table = xl_book.worksheets[i]
            data = []
            for row in table.values:
                r = []
                for one in row:
                    # if one is not None:
                    r.append(one)
                data.append(r)
            datas.append(data)
        return datas
    def Table(self,data):
        table = QTableWidget()
        table.setRowCount(len(data)) # 设置行数
        table.setColumnCount(len(data[0]))  # 设置列数
        # headers = tuple("ABCDEFGHIJKLMNOPQRSTUVWXYZ")
        # self.table.setHorizontalHeaderLabels(headers[0:ncols])
        # 遍历Excel数据并插入到表格控件中
        row = 0
        for row_data in data:
            col = 0
            for cell in row_data:
                item = QTableWidgetItem(str(cell))
                table.setItem(row, col, item)
                col += 1
            row += 1
        return table
    def __init__(self, parent): 
        super().__init__(parent) 
        self.layout = QVBoxLayout(self) 
        # self.datas=self.getBook(os.path.join(getpath.getpath(),"ejff.xlsx"))
        self.tab_widget = QTabWidget() 
        # for i in range(len(self.datas)):
        #     one=self.Table(self.datas[i])
        #     self.tab_widget.addTab(one,"sheet"+str(i+1))
        self.tab_widget.setTabPosition(QTabWidget.South)
        self.layout.addWidget(self.tab_widget) 
if __name__ == '__main__': 
    app = QApplication(sys.argv) 
    ex = App() 
    sys.exit(app.exec_()) 
